﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using LibrarySolution.Model.基础;
using LibrarySolution.DB;

namespace LibrarySolution.DAO.基础
{
    class GlyDao
    {
        String sql;
        List<SqlParameter> list;
        DataTable dt = new DataTable();
        /// <summary>
        ///  登录
        /// </summary>
        /// <param name="glyPwd"></param>
        /// <returns></returns>
        public GlyEntity doLogin(string glyZh,string glyPwd)
        {
            GlyEntity glyEntity = null;
            SqlParameter[] parameters = { new SqlParameter("@glyPwd", glyPwd),
                                            new SqlParameter("@glyZh",glyZh)};
            sql = "select gly_code,gly_zh,gly_pwd,gly_name,gly_sex,gly_lxdh,gly_lxdz from jc_gly " +
                "where gly_zh = @glyZh and gly_pwd = @glyPwd";
            string[][] result = DBExecuteUtil.querySqlArray(sql, parameters);

            if (result != null && result.Length > 0)
            {
                glyEntity = new GlyEntity();
                glyEntity.gly_code = result[0][0];
                glyEntity.gly_zh = result[0][1];
                glyEntity.gly_pwd = result[0][2];
                glyEntity.gly_name = result[0][3];
                glyEntity.gly_sex = result[0][4];
                glyEntity.gly_lxdh = result[0][5];
                glyEntity.gly_lxdz = result[0][6];
            }
            return glyEntity;
        }

        public String[] getGlyZh()
        {
            String[] glyZH;
            String[][] result;
            sql = "select gly_zh from jc_gly";
            result = DBExecuteUtil.querySqlArray(sql, null);
            glyZH = new String[result.Length];
            for (int i = 0; i < result.Length; i++)
            {
                glyZH[i] = result[i][0];
            }
            return glyZH;
        }

        public String getGlyCodeByZh(String glyZh)
        {
            sql = "select gly_code from jc_gly where gly_zh=@glyZh";
            SqlParameter[] parameters = { new SqlParameter("@glyZh", glyZh) };
            String result = DBExecuteUtil.querySql(sql, parameters);
            return result;
        }

        public String getYhPwdByNo(string yhNo)
        {
            sql = "select gly_pwd from jc_gly where gly_zh = @glyZh";
            SqlParameter[] parameters = { new SqlParameter("@glyZh", yhNo) };
            String result = DBExecuteUtil.querySql(sql,parameters);
            return result;
        }

        public DataTable getDataTable(GlyEntity glyEntity)
        {
            GlyxxQuery(glyEntity);
            dt = DBExecuteUtil.querySqlGetTable(sql, list.ToArray());
            return dt;
        }

        public GlyEntity getGlyxxByCode(GlyEntity glyEntity)
        {
            String[][] result;
            GlyxxQuery(glyEntity);
            result = DBExecuteUtil.querySqlArray(sql, list.ToArray());
            if (result != null)
            {
                glyEntity.gly_code = result[0][0];
                glyEntity.gly_zh = result[0][1];
                glyEntity.gly_pwd = result[0][2];
                glyEntity.gly_name = result[0][3];
                glyEntity.gly_sex = result[0][4];
                glyEntity.gly_lxdh = result[0][5];
                glyEntity.gly_lxdz = result[0][6];
            }
            return glyEntity;
        }

        public void updateGlyxx(GlyEntity glyEntity)
        {
            GlyxxQuery(glyEntity);
            sql = "update jc_gly set gly_zh = @glyZh, gly_pwd = @glyPwd, gly_name = @glyName," +
                "gly_sex = @glySex, gly_lxdh = @Lxdh, gly_lxdz = @Lxdz where gly_code = @glyCode";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        public void updataGlyPwd(GlyEntity glyEntity)
        {
            GlyxxQuery(glyEntity);
            sql = "update jc_gly set gly_pwd = @glyPwd where gly_zh = @glyZh";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        public void insertGly(GlyEntity glyEntity)
        {
            GlyxxQuery(glyEntity);
            sql = " insert into jc_gly values(@glyZh,@glyPwd,@glyName,@glySex," +
                "@Lxdh,@Lxdz)";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        public String getMaxGlyCode()
        {
            String result;
            sql = "select max(gly_code) from jc_gly";
            result = DBExecuteUtil.querySql(sql, null);
            return Convert.ToString((int.Parse(result) + 1));
        }

        public void delSelectRow(string glyCode)
        {
            String sql = "delete from jc_gly where gly_code = @glyCode";
            SqlParameter[] parameters = { new SqlParameter("@glyCode", glyCode) };
            DBExecuteUtil.querySqlV(sql, parameters);
        }

        private void GlyxxQuery(GlyEntity glyEntity)
        {
            list = new List<SqlParameter>();
            sql = "select gly_code as  管理员编码,gly_zh as 账号,gly_pwd as 密码,gly_name as 姓名," +
                "gly_sex as 性别,gly_lxdh as 联系电话,gly_lxdz as 联系地址 from jc_gly";
            if (glyEntity.gly_code != null)
            {
                list.Add(new SqlParameter("@glyCode", glyEntity.gly_code));
                sql += " where gly_code = @glyCode";
                if (glyEntity.gly_zh != null)
                {
                    list.Add(new SqlParameter("@glyZh", glyEntity.gly_zh));
                    sql += " and gly_zh = @glyZh";
                }
                if (glyEntity.gly_pwd != null)
                {
                    list.Add(new SqlParameter("@glyPwd", glyEntity.gly_pwd));
                    sql +=" and gly_pwd = @glyPwd";
                }
                if (glyEntity.gly_name != null)
                {
                    list.Add(new SqlParameter("@glyName", glyEntity.gly_name));
                    sql += " and gly_name = @glyName";
                }
                if (glyEntity.gly_sex != null)
	            {
                    list.Add(new SqlParameter("@glySex",glyEntity.gly_sex));
                    sql += " and gly_sex = @glySex";
	            }
                if (glyEntity.gly_lxdh != null)
                {
                    list.Add(new SqlParameter("@Lxdh", glyEntity.gly_lxdh));
                    sql += " and gly_lxdh = @Lxdh";
                }
                if (glyEntity.gly_lxdz != null)
                {
                    list.Add(new SqlParameter("@Lxdz", glyEntity.gly_lxdz));
                    sql += " and gly_lxdz = @Lxdz";
                }
            }
        }

    }
}
